package com.su.utils;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * 导入工具类
 */

public class ImportExcelUtil {

    private static final ServletFileUpload SERVLET_FILE_UPLOAD =
            new ServletFileUpload(new DiskFileItemFactory(0, null));

    /**
     * 导入数据解析
     *
     * @param request
     * @return
     */
    public static Map<String, Object> importParse(HttpServletRequest request) {
        return importParse(request, 0);
    }

    public static FileItem getFileItem(HttpServletRequest request, String fieldName) throws FileUploadException {
        if (StringUtils.isEmpty(fieldName)) {
            return null;
        }

        for (FileItem fileItem : getFileItems(request)) {
            if (fieldName.equals(fileItem.getFieldName())) {
                return fileItem;
            }
        }
        return null;
    }

    public static List<FileItem> getFileItems(HttpServletRequest request) throws FileUploadException {
        if (null == request) {
            return Collections.emptyList();
        }

        return SERVLET_FILE_UPLOAD.parseRequest(request);
    }

    public static List<List<String>> parse(FileItem fileItem) throws Exception {
        if (null == fileItem) {
            return Collections.emptyList();
        }

        return new ImportExecl().read(fileItem, 1);
    }

    /**
     * 导入数据解析，从第i行提取数据
     *
     * @param request
     * @param i
     * @return
     */
    public static Map<String, Object> importParse(HttpServletRequest request, int i) {
        Map<String, Object> result = new HashMap<>();
        try {
            List<FileItem> items = getFileItems(request);
            Iterator iter = items.iterator();
            while (iter.hasNext()) {
                FileItem item = (FileItem) iter.next();
                if (!item.isFormField()) {
                    ImportExecl poi = new ImportExecl();
                    List<List<String>> list = poi.read(item, i);
                    result.put(item.getFieldName(), list);
                } else {
                    result.put(item.getFieldName(), item.getString("UTF-8"));
                }
            }


        } catch (Exception e) {
            new RuntimeException("导入解析异常:" + e.toString());
        }
        return result;

    }

    public static String getFieldInfo(HttpServletRequest request, String fieldName) {
        try {
            FileItemFactory factory = new DiskFileItemFactory();
            ServletFileUpload upload = new ServletFileUpload(factory);
            List<FileItem> items = upload.parseRequest(request);
            Iterator iter = items.iterator();
            while (iter.hasNext()) {
                FileItem item = (FileItem) iter.next();
                if (fieldName.equals(item.getFieldName())) {
                    return item.getString();
                }
            }

        } catch (Exception e) {
            new RuntimeException("导入解析字段异常:" + e.toString());
        }
        return null;
    }



    /**
     * excel读取 工具类
     *
     * @jar包 该类使用到了以下jar：
     * 1、poi-ooxml-3.9.jar
     * 2、poi-3.9.jar
     */
    private static class ImportExecl {

        //总行数
        private int totalRows = 0;

        //总列数
        private int totalCells = 0;

        //错误信息
        private String errorInfo;

        //构造方法
        public ImportExecl() {
        }

        /**
         * 得到总行数
         */
        public int getTotalRows() {
            return totalRows;
        }

        /**
         * 得到总列数
         */
        public int getTotalCells() {
            return totalCells;
        }

        /**
         * 得到错误信息
         */
        public String getErrorInfo() {
            return errorInfo;
        }

        /**
         * 验证excel文件
         */
        public boolean validateExcel(String filePath) {
            /** 检查文件名是否为空或者是否是Excel格式的文件 */
            if (filePath == null || !(CheckExcelUtil.isExcel2003(filePath) || CheckExcelUtil.isExcel2007(filePath))) {
                errorInfo = "文件名不是excel格式";
                return false;
            }

            /** 检查文件是否存在 */
            File file = new File(filePath);
            if (file == null || !file.exists()) {
                errorInfo = "文件不存在";
                return false;
            }
            return true;
        }

        /*   *//**
         * 根据文件路径读取excel文件
         *//*
        public List<List<String>> read(String filePath) throws IOException {
            List<List<String>> dataLst = new ArrayList<List<String>>();
            InputStream is = null;
            try {
                *//** 验证文件是否合法 *//*
                if (!validateExcel(filePath)) {
                    System.out.println(errorInfo);
                    return null;
                }

                *//** 判断文件的类型，是2003还是2007 *//*
                boolean isExcel2003 = true;
                if (CheckExcelUtil.isExcel2007(filePath)) {
                    isExcel2003 = false;
                }

                *//** 调用本类提供的根据流读取的方法 *//*
                File file = new File(filePath);
                is = new FileInputStream(file);
                dataLst = read(is, isExcel2003);
                is.close();
                is = null;
            } catch (Exception ex) {
                ex.printStackTrace();
            } finally {
                if (is != null) {
                    try {
                        is.close();
                    } catch (IOException e) {
                        is = null;
                        e.printStackTrace();
                    }
                }
            }
            return dataLst;
        }*/

        /**
         * 根据流读取Excel文件
         *
         * @return
         * @扩展说明 如果使用springmvc的MultipartFile接收前端上传的excel文件的话，可以使用MultipartFile的对象，获取上传的文件名称，
         * 然后，可以通过 CheckExcelUtil 类的方法，接收文件名称参数，来判断excel所属的版本。最后再调用此方法来读取excel数据。
         */
        public List<List<String>> read(FileItem item, int i) throws Exception {
            InputStream inputStream = null;
            try {
                inputStream = item.getInputStream();
            } catch (IOException e) {

            }
            if (inputStream == null) {
                return null;
            }
            List<List<String>> dataLst = null;
            //office excel

            Workbook wb;
            if (item.getName().endsWith(".xls")) {
                wb = new HSSFWorkbook(inputStream);
            } else {
                wb = new XSSFWorkbook(inputStream);
            }
            dataLst = read(wb, i);


            return dataLst;
        }

        /**
         * 读取数据
         */
        private List<List<String>> read(Workbook wb, int initRowCount) {
            List<List<String>> dataLst = new ArrayList<>();
            //得到第一个shell
            Sheet sheet = wb.getSheetAt(0);
            //得到Excel的行数
            this.totalRows = sheet.getPhysicalNumberOfRows();
            //得到Excel的列数
            if (this.totalRows >= 1 && sheet.getRow(0) != null) {
                this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            }

            //循环Excel的行
            int i = initRowCount;
            //连续遇到遇到空行数
            int blankRowCount = 0;
            while (true) {
                Row row = sheet.getRow(i++);
                if (row == null) {
                    if (blankRowCount++ > 3) {
                        break;
                    }
                    continue;
                } else {
                    blankRowCount = 0;
                }
                List<String> rowLst = new ArrayList<String>();
                //循环Excel的列
                for (int c = 0; c < this.getTotalCells(); c++) {
                    Cell cell = row.getCell(c);
                    String cellValue = "";
                    if (null != cell) {
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING); //把所有的Excel内容当做字符串处理
                        // 以下是判断数据的类型
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                cellValue = cell.getNumericCellValue() + "";
                                break;

                            case HSSFCell.CELL_TYPE_STRING: // 字符串
                                cellValue = cell.getStringCellValue();
                                break;

                            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                cellValue = cell.getBooleanCellValue() + "";
                                break;

                            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                                cellValue = cell.getCellFormula() + "";
                                break;

                            case HSSFCell.CELL_TYPE_BLANK: // 空值
                                cellValue = "";
                                break;

                            case HSSFCell.CELL_TYPE_ERROR: // 故障
                                cellValue = "非法字符";
                                break;

                            default:
                                cellValue = "未知类型";
                                break;
                        }
                    }
                    //保存第r行的第c列
                    rowLst.add(cellValue.trim());
                }
                dataLst.add(rowLst);

            }
            return dataLst;
        }

    }

    private static class CheckExcelUtil {
        /**
         * 检查是否是2003的excel，若是，则返回true
         */
        public static boolean isExcel2003(String filePath) {
            return filePath.matches("^.+\\.(?i)(xls)$");
        }

        /**
         * 检查是否是2007的excel，若是，则返回true
         */
        public static boolean isExcel2007(String filePath) {
            return filePath.matches("^.+\\.(?i)(xlsx)$");
        }
    }


}
